
  ___  ____  ____  ____  ____ (R)
 /__    /   ____/   /   ____/
___/   /   /___/   /   /___/   13.0   Copyright 1985-2013 StataCorp LP
  Statistics/Data Analysis            StataCorp
                                      4905 Lakeway Drive
     Special Edition                  College Station, Texas 77845 USA
                                      800-STATA-PC        http://www.stata.com
                                      979-696-4600        stata@stata.com
                                      979-696-4601 (fax)

16-user Stata network perpetual license:
       Serial number:  401306212364
         Licensed to:  Econometrics Laboratory
                       UC Berkeley

Notes:
      1.  (-v# option or -set maxvar-) 5000 maximum variables
      2.  Command line editing disabled
      3.  Stata running in batch mode

Note:  Your site can add messages to the introduction by editing the file
       stata.msg in the directory where Stata is installed.

. do build_data 

. * This file merges all the datasets used 
. ***********************************************
. clear all

. set mem 9000m
set memory ignored.
    Memory no longer needs to be set in modern Statas; memory adjustments are
    performed on the fly automatically.

. set matsize 2000

. set maxvar 8000


. 
. 
. 
. **************************
. * TVA DUMMIES
. **************************
. infile fips using ~/tva/data/tva_county_dummy/tvacounties.txt
(196 observations read)

. summ

    Variable |       Obs        Mean    Std. Dev.       Min        Max
-------------+--------------------------------------------------------
        fips |       196    33855.66     15398.9       1015      51195

. g tva = 1

. sort fips

. save tmp, replace
file tmp.dta saved

. 
. ***************************
. * READ STATE-LEVEL DATA
. * AND TAKE 1930 INCOME VARIABLE
. ***************************
. u ~/tva/data/valentina/state_variables/state_level_data

. keep state pcp_income_30

. sort state

. save tmp7, replace
file tmp7.dta saved

. 
. 
. ***************************
. * WAGE DATA
. * THESE ARE THE
. * CORRECT MANUF AND TRADE WAGES.
. ******************************
. u ~/tva/data/valentina/county_variables/new/tva1
(COUNTY & CITY DATA BOOK, 2000: Counties)

. keep fips mwage* pcmwage* pctwage* var88_county72 var89_county72

. duplicates report fips

Duplicates in terms of fips

--------------------------------------
   copies | observations       surplus
----------+---------------------------
        1 |         3130             0
        2 |            2             1
       88 |           88            87
--------------------------------------

. duplicates drop fips, force

Duplicates in terms of fips

(88 observations deleted)

. sort fips

. save tmp76, replace
file tmp76.dta saved

. 
. 
. ***************************
. * COUNTY-LEVEL DATA
. ***************************
. u ~/tva/data/valentina/county_variables/tva_update //this file is built by tv
> a_update.do (certified by Valentina 7/14/2011)
(County and City Data Book [United States] Consolidated File: County Data, 1947
> -1)

. duplicates report fips

Duplicates in terms of fips

--------------------------------------
   copies | observations       surplus
----------+---------------------------
        1 |         2980             0
--------------------------------------

. drop mwage* twage*

. drop _merge

. sort fips

. merge 1:1 fips using tmp76

    Result                           # of obs.
    -----------------------------------------
    not matched                           152
        from master                         0  (_merge==1)
        from using                        152  (_merge==2)

    matched                             2,980  (_merge==3)
    -----------------------------------------

. tab _merge

                 _merge |      Freq.     Percent        Cum.
------------------------+-----------------------------------
         using only (2) |        152        4.85        4.85
            matched (3) |      2,980       95.15      100.00
------------------------+-----------------------------------
                  Total |      3,132      100.00

. keep if _merge ==3
(152 observations deleted)

. drop _merge

. 
. merge 1:1 fips using tmp

    Result                           # of obs.
    -----------------------------------------
    not matched                         2,796
        from master                     2,790  (_merge==1)
        from using                          6  (_merge==2)

    matched                               190  (_merge==3)
    -----------------------------------------

. tab _merge

                 _merge |      Freq.     Percent        Cum.
------------------------+-----------------------------------
        master only (1) |      2,790       93.44       93.44
         using only (2) |          6        0.20       93.64
            matched (3) |        190        6.36      100.00
------------------------+-----------------------------------
                  Total |      2,986      100.00

. drop if _merge ==2
(6 observations deleted)

. replace tva = 0 if tva ==.
(2790 real changes made)

. 
. 
. ****************************
. *  Merge on Fishback Data  *
. ****************************
. drop _merge

. drop if county==.|state==.
(6 observations deleted)

. 
. merge 1:1 county state using /accounts/projects/tva/tva/data/topography/fishb
> ack

    Result                           # of obs.
    -----------------------------------------
    not matched                           128
        from master                        27  (_merge==1)
        from using                        101  (_merge==2)

    matched                             2,947  (_merge==3)
    -----------------------------------------

. tab _merge

                 _merge |      Freq.     Percent        Cum.
------------------------+-----------------------------------
        master only (1) |         27        0.88        0.88
         using only (2) |        101        3.28        4.16
            matched (3) |      2,947       95.84      100.00
------------------------+-----------------------------------
                  Total |      3,075      100.00

. drop if _merge==2
(101 observations deleted)

. drop N10

. 
. 
. ****************************
. *  Merge on Ag Land Values *
. ****************************
. drop _merge

. merge 1:1 fips using /accounts/projects/tva/tva/data/agricultural_land/data

    Result                           # of obs.
    -----------------------------------------
    not matched                           229
        from master                         5  (_merge==1)
        from using                        224  (_merge==2)

    matched                             2,969  (_merge==3)
    -----------------------------------------

. tab _merge

                 _merge |      Freq.     Percent        Cum.
------------------------+-----------------------------------
        master only (1) |          5        0.16        0.16
         using only (2) |        224        7.00        7.16
            matched (3) |      2,969       92.84      100.00
------------------------+-----------------------------------
                  Total |      3,198      100.00

. drop if _merge==2
(224 observations deleted)

. duplicates report fips

Duplicates in terms of fips

--------------------------------------
   copies | observations       surplus
----------+---------------------------
        1 |         2974             0
--------------------------------------

. 
. 
. ****************************
. *  Merge on TVA 'Donut'    *
. ****************************
. drop _merge

. *merge 1:1 fips using /accounts/projects/tva/tva/data/border_counties/donut
. joinby fips using /accounts/projects/tva/tva/data/border_counties/donut, unma
> tched(both)

. tab _merge

                       _merge |      Freq.     Percent        Cum.
------------------------------+-----------------------------------
          only in master data |      2,889       97.04       97.04
           only in using data |          3        0.10       97.14
both in master and using data |         85        2.86      100.00
------------------------------+-----------------------------------
                        Total |      2,977      100.00

. drop if _merge==2
(3 observations deleted)

. 
. 
. ****************************
. *  Merge on Correct Employment     
. * Variables
. ****************************
. drop _merge

. merge 1:1 fips using ~/tva/data/valentina/county_variables/enrico/enrico_jobs

    Result                           # of obs.
    -----------------------------------------
    not matched                            82
        from master                         0  (_merge==1)
        from using                         82  (_merge==2)

    matched                             2,974  (_merge==3)
    -----------------------------------------

. 
. tab _merge

                 _merge |      Freq.     Percent        Cum.
------------------------+-----------------------------------
         using only (2) |         82        2.68        2.68
            matched (3) |      2,974       97.32      100.00
------------------------+-----------------------------------
                  Total |      3,056      100.00

. drop if _merge==2
(82 observations deleted)

. duplicates report fips

Duplicates in terms of fips

--------------------------------------
   copies | observations       surplus
----------+---------------------------
        1 |         2974             0
--------------------------------------

. 
. 
. *******************************
. *  Merge on Housing Val/Rents *
. *******************************
. drop _merge

. merge 1:1 fips using ~/tva/data/valentina/county_variables/RawData/housingval
> s

    Result                           # of obs.
    -----------------------------------------
    not matched                           192
        from master                         1  (_merge==1)
        from using                        191  (_merge==2)

    matched                             2,973  (_merge==3)
    -----------------------------------------

. tab _merge

                 _merge |      Freq.     Percent        Cum.
------------------------+-----------------------------------
        master only (1) |          1        0.03        0.03
         using only (2) |        191        6.03        6.07
            matched (3) |      2,973       93.93      100.00
------------------------+-----------------------------------
                  Total |      3,165      100.00

. drop if _merge==2
(191 observations deleted)

. duplicates report fips

Duplicates in terms of fips

--------------------------------------
   copies | observations       surplus
----------+---------------------------
        1 |         2974             0
--------------------------------------

. 
. drop _merge

. merge 1:1 fips using ~/tva/data/valentina/county_variables/RawData/county62_1
> , keepusing(var61_county62 var63_county62)

    Result                           # of obs.
    -----------------------------------------
    not matched                           215
        from master                         2  (_merge==1)
        from using                        213  (_merge==2)

    matched                             2,972  (_merge==3)
    -----------------------------------------

. 
. ren var61_county62 medhsval60

. ren var63_county62 medrnt60

. tab _merge

                 _merge |      Freq.     Percent        Cum.
------------------------+-----------------------------------
        master only (1) |          2        0.06        0.06
         using only (2) |        213        6.68        6.75
            matched (3) |      2,972       93.25      100.00
------------------------+-----------------------------------
                  Total |      3,187      100.00

. drop if _merge==2
(213 observations deleted)

. duplicates report fips

Duplicates in terms of fips

--------------------------------------
   copies | observations       surplus
----------+---------------------------
        1 |         2974             0
--------------------------------------

. 
. 
. 
. *******************************
. * Merge on weather variables  *
. *******************************
. drop _merge

. 
. merge 1:1 fips using /accounts/projects/tva/tva/data/weather/JUL_MEAN_IDW200_
> 365_1968_2002.dta

    Result                           # of obs.
    -----------------------------------------
    not matched                           145
        from master                         6  (_merge==1)
        from using                        139  (_merge==2)

    matched                             2,968  (_merge==3)
    -----------------------------------------

. foreach var of varlist tmin tmean tmax{
  2.         ren `var' `var'_jul
  3.         }

.         drop if _merge==2
(139 observations deleted)

.         drop _merge

. 
.         merge 1:1 fips using /accounts/projects/tva/tva/data/weather/JAN_MEAN
> _IDW200_365_1968_2002.dta

    Result                           # of obs.
    -----------------------------------------
    not matched                           145
        from master                         6  (_merge==1)
        from using                        139  (_merge==2)

    matched                             2,968  (_merge==3)
    -----------------------------------------

.         foreach var of varlist tmin tmean tmax{
  2.                 ren `var' `var'_jan
  3.                 }

.                 drop if _merge==2
(139 observations deleted)

. 
. 
. ****************************
. * Merge extra manuf vars   *
. ****************************
. drop _merge

. merge 1:1 fips using ~/tva/data/valentina/county_variables/enrico/manuf2/data
> 2.dta

    Result                           # of obs.
    -----------------------------------------
    not matched                           157
        from master                         0  (_merge==1)
        from using                        157  (_merge==2)

    matched                             2,974  (_merge==3)
    -----------------------------------------

. tab _merge

                 _merge |      Freq.     Percent        Cum.
------------------------+-----------------------------------
         using only (2) |        157        5.01        5.01
            matched (3) |      2,974       94.99      100.00
------------------------+-----------------------------------
                  Total |      3,131      100.00

. drop if _merge==2
(157 observations deleted)

. 
. 
. drop _merge

. merge 1:1 fips using ~/tva/data/valentina/county_variables/enrico/manuf3/tva.
> dta

    Result                           # of obs.
    -----------------------------------------
    not matched                           168
        from master                        26  (_merge==1)
        from using                        142  (_merge==2)

    matched                             2,948  (_merge==3)
    -----------------------------------------

. tab _merge

                 _merge |      Freq.     Percent        Cum.
------------------------+-----------------------------------
        master only (1) |         26        0.83        0.83
         using only (2) |        142        4.56        5.39
            matched (3) |      2,948       94.61      100.00
------------------------+-----------------------------------
                  Total |      3,116      100.00

. drop if _merge==2
(142 observations deleted)

. 
. drop _merge

. merge 1:1 fips using ~/tva/data/valentina/county_variables/new/new/tva3.dta

    Result                           # of obs.
    -----------------------------------------
    not matched                           156
        from master                         0  (_merge==1)
        from using                        156  (_merge==2)

    matched                             2,974  (_merge==3)
    -----------------------------------------

. tab _merge

                 _merge |      Freq.     Percent        Cum.
------------------------+-----------------------------------
         using only (2) |        156        4.98        4.98
            matched (3) |      2,974       95.02      100.00
------------------------+-----------------------------------
                  Total |      3,130      100.00

. drop if _merge==2
(156 observations deleted)

. summ *merge*

    Variable |       Obs        Mean    Std. Dev.       Min        Max
-------------+--------------------------------------------------------
     _merge1 |      2974    .9088769    .2878324          0          1
     _merge2 |      2974    .9515804    .2146874          0          1
     _merge3 |      2974    .9515804    .2146874          0          1
     _merge4 |      2974    .9895763      .10158          0          1
     _merge5 |      2974           1           0          1          1
-------------+--------------------------------------------------------
     _merge6 |      2974           1           0          1          1
  _merge1_80 |      2974           0           0          0          0
  _merge2_80 |      2974           0           0          0          0
  _merge3_80 |      2974           0           0          0          0
     _merge7 |      2974    .9983188    .0409753          0          1
-------------+--------------------------------------------------------
  _merge1_90 |      2974           0           0          0          0
  _merge2_90 |      2974           0           0          0          0
  _merge3_90 |      2974           0           0          0          0
     _merge8 |      2974    .9979825    .0448786          0          1
_merge3_2000 |      2974           0           0          0          0
-------------+--------------------------------------------------------
     _merge9 |      2974    .9976463    .0484663          0          1
    _merge10 |      2974    .9983188    .0409753          0          1
    _merge11 |      2974    .9983188    .0409753          0          1
    _merge12 |      2974    .9983188    .0409753          0          1
    _merge13 |      2974    .9996638    .0183371          0          1
-------------+--------------------------------------------------------
      _merge |      2974           3           0          3          3

. drop _merge*

. 
. 
. ****************************
. * PROPOSED AUTHORITIES
. ******************************
. sort fips

. merge fips using ~/tva/data/authorities/data5/data
(note: you are using old merge syntax; see [D] merge for new syntax)

. tab _merge

     _merge |      Freq.     Percent        Cum.
------------+-----------------------------------
          2 |          6        0.20        0.20
          3 |      2,974       99.80      100.00
------------+-----------------------------------
      Total |      2,980      100.00

. drop if _merge==2
(6 observations deleted)

. drop _merge

. tab aut_euclidean1

aut_euclide |
        an1 |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |      2,775       93.31       93.31
          1 |        199        6.69      100.00
------------+-----------------------------------
      Total |      2,974      100.00

. tab aut_euclidean2

aut_euclide |
        an2 |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |      2,748       92.40       92.40
          1 |        226        7.60      100.00
------------+-----------------------------------
      Total |      2,974      100.00

. tab aut_euclidean3

aut_euclide |
        an3 |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |      2,796       94.01       94.01
          1 |        178        5.99      100.00
------------+-----------------------------------
      Total |      2,974      100.00

. tab aut_cent

 aut_centr4 |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |      2,724       91.59       91.59
          1 |        250        8.41      100.00
------------+-----------------------------------
      Total |      2,974      100.00

. tab aut_euclidean5

aut_euclide |
        an5 |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |      2,931       98.55       98.55
          1 |         43        1.45      100.00
------------+-----------------------------------
      Total |      2,974      100.00

. tab aut_euclidean6

aut_euclide |
        an6 |      Freq.     Percent        Cum.
------------+-----------------------------------
          0 |      2,931       98.55       98.55
          1 |         43        1.45      100.00
------------+-----------------------------------
      Total |      2,974      100.00

. g       aut4 =0

. replace aut4 =1  if max(aut_euclidean1,aut_euclidean2,aut_euclidean3,aut_cent
> ,aut_euclidean5,aut_euclidean6) ==1
(939 real changes made)

. summ fips tva aut_*

    Variable |       Obs        Mean    Std. Dev.       Min        Max
-------------+--------------------------------------------------------
        fips |      2974     29780.6    14652.52       1001      56047
         tva |      2974     .063887    .2445927          0          1
aut_euclid~1 |      2974    .0669132    .2499137          0          1
aut_euclid~2 |      2974    .0759919    .2650298          0          1
aut_euclid~3 |      2974    .0598521    .2372524          0          1
-------------+--------------------------------------------------------
  aut_centr4 |      2974    .0840619    .2775272          0          1
aut_euclid~5 |      2974    .0144586    .1193917          0          1
aut_euclid~6 |      2974    .0144586    .1193917          0          1

. tab tva aut4

           |         aut4
       tva |         0          1 |     Total
-----------+----------------------+----------
         0 |     1,845        939 |     2,784 
         1 |       190          0 |       190 
-----------+----------------------+----------
     Total |     2,035        939 |     2,974 


. drop aut_eucl* aut_cent*

. 
. 
. 
. 
. ****************************
. *  Cleanup and Definition  *
. ****************************
. 
. * State and region
. drop state

. g state = int(fips/1000)

. drop if state ==51 | state ==52 | state ==2 | state ==3 | state ==15
(0 observations deleted)

. g northeast =0

. g midwest=0

. g south=0

. g west=0

.                 replace northeast =1 if state == 9 | state == 23 | state == 2
> 5 | state == 33 | state == 44 | state  == 50 | state == 34 | state == 36 | st
> ate == 42
(217 real changes made)

.                 replace midwest=1    if state == 17 | state == 18 | state == 
> 26 | state == 39 | state == 55 | state ==19 | state == 20 | state == 27 | sta
> te == 29 | state == 31 | state == 38 | state == 46
(1056 real changes made)

.                 replace south=1      if state ==10 | state == 11 | state == 1
> 2 | state == 13 | state == 24 | state  == 37 | state == 45 | state == 51   | 
> state == 54 | state == 1 | state == 21 | state == 28  | state == 47 | state =
> = 5 | state == 22 | state == 40 | state == 48
(1289 real changes made)

.                 replace west=1       if state == 4 | state == 8 | state == 16
>  | state == 30 | state == 32 | state == 35 | state == 49 | state == 56 | stat
> e == 2 | state == 6 | state == 15 | state == 41 | state ==53
(412 real changes made)

.                 g       region =1 if northeast ==1
(2757 missing values generated)

.                 replace region =2 if midwest ==1
(1056 real changes made)

.                 replace region =3 if south ==1
(1289 real changes made)

.                 replace region =4 if west ==1
(412 real changes made)

. 
. 
. * CPI
. g cpi890= 5 

. g cpi0  = 7

. g cpi10 = 9

. g cpi20 = 20

. g cpi30 = 16.7

. g cpi40 = 14

. g cpi50 = 24.1

. g cpi60 = 29.6

. g cpi70 = 38.8

. g cpi80 = 82.4

. g cpi90 = 130.7

. g cpi2000 = 172.2

. 
. 
. * Merge the state-level variable
. sort state

. merge state using tmp7
(note: you are using old merge syntax; see [D] merge for new syntax)
variable state does not uniquely identify observations in the master data

. tab _merge

     _merge |      Freq.     Percent        Cum.
------------+-----------------------------------
          2 |         12        0.40        0.40
          3 |      2,974       99.60      100.00
------------+-----------------------------------
      Total |      2,986      100.00

. drop if tva ==.
(12 observations deleted)

. rm tmp.dta

. rm tmp7.dta

. rm tmp76.dta

. 
. 
. ***********************************************
. *Drop Donut and counties with missing lat/long*
. ***********************************************
. drop if border_county==1|latitude==.|longitud==.
(112 observations deleted)

. 
. 
. *******************************************************
. * Drop counties with very low populations in any year *
. *******************************************************
. drop if pop0<1000|pop10<1000|pop20<1000|pop30<1000|pop40<1000|pop50<1000|pop6
> 0<1000|pop70<1000|pop80<1000|pop90<1000|pop2000<1000
(99 observations deleted)

. 
. 
. 
. save build, replace
file build.dta saved

. 
. 
. 
. 
. 
. 
end of do-file
